"""6.5.8 contrainte unique task mention rel

Revision ID: ea80e8805df8
Revises: e26f31c51194
Create Date: 2023-02-13 15:08:09.658675

"""

# revision identifiers, used by Alembic.
revision = "ea80e8805df8"
down_revision = "e26f31c51194"

from alembic import op
import sqlalchemy as sa
from caerp_base.models.base import DBBASE, default_table_args
from caerp.alembic.utils import disable_constraints, enable_constraints, table_exists


def clean_table(session, tablename):
    rel_table = sa.Table(
        tablename,
        DBBASE.metadata,
        sa.Column("task_id", sa.Integer, sa.ForeignKey("task.id")),
        sa.Column("mention_id", sa.Integer, sa.ForeignKey("task_mention.id")),
        sa.UniqueConstraint("task_id", "mention_id"),
        mysql_charset=default_table_args["mysql_charset"],
        mysql_engine=default_table_args["mysql_engine"],
        extend_existing=True,
    )
    for entry in (
        session.query(rel_table, sa.func.count(rel_table.c.task_id))
        .group_by("task_id", "mention_id")
        .having(sa.func.count(rel_table.c.task_id) > 1)
        .all()
    ):
        op.execute(
            f"DELETE FROM {tablename} where task_id={entry[0]} and mention_id={entry[1]} LIMIT {entry[2]-1};"
        )


def pre_migrate_datas():
    from alembic.context import get_bind
    from zope.sqlalchemy import mark_changed
    from caerp_base.models.base import DBSESSION

    session = DBSESSION()
    conn = get_bind()
    for table in ("task_mention_rel", "mandatory_task_mention_rel"):
        clean_table(session, table)
    mark_changed(session)
    session.flush()


def update_database_structure():
    # ### commands auto generated by Alembic - please adjust! ###
    disable_constraints()
    for table in (
        "progress_invoicing_base_element",
        "progress_invoicing_base_status",
        "progress_invoicing_line_status",
        "progress_invoicing_group_status",
        "progress_invoicing_group",
        "progress_invoicing_line",
    ):
        if table_exists(table):
            op.drop_table(table)

    op.create_unique_constraint(
        op.f("uq_task_mention_rel_task_id"),
        "task_mention_rel",
        ["task_id", "mention_id"],
    )
    op.create_unique_constraint(
        op.f("uq_mandatory_task_mention_rel_task_id"),
        "mandatory_task_mention_rel",
        ["task_id", "mention_id"],
    )
    enable_constraints()
    # ### end Alembic commands ###


def upgrade():
    pre_migrate_datas()
    update_database_structure()


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(
        op.f("uq_task_mention_rel_task_id"), "task_mention_rel", type_="unique"
    )
    # ### end Alembic commands ###
